<?php 
class product_plugin_product extends abstract_product_plugin {
 
    public function getProduct($product_id){
        
        $query_product = $this->pdo->prepare("SELECT DISTINCT *, 
            pd.name AS name, p.image, m.name AS manufacturer, 
            (SELECT price FROM " . $this->db_prefix . "product_discount pd2 
            WHERE pd2.product_id = p.product_id 
            AND pd2.user_group_id = :user_group_id 
            AND pd2.quantity = '1' 
            AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) 
                AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) 
            ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) 
            AS discount, 

            (SELECT price FROM " . $this->db_prefix . "product_special ps 
            WHERE ps.product_id = p.product_id 
            AND ps.user_group_id = :user_group_id 
            AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) 
            AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) 
            ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) 
            AS special, 

            (SELECT points FROM " . $this->db_prefix . "product_reward pr 
            WHERE pr.product_id = p.product_id 
            AND user_group_id = :user_group_id) 
            AS reward, 

            (SELECT ss.name FROM " . $this->db_prefix . "stock_status ss 
            WHERE ss.stock_status_id = p.stock_status_id 
            AND ss.language_id = :language_id) 
            AS stock_status, 

            (SELECT wcd.unit FROM " . $this->db_prefix . "weight_class_description wcd 
            WHERE p.weight_class_id = wcd.weight_class_id 
            AND wcd.language_id = :language_id) 
            AS weight_class, 

            (SELECT lcd.unit FROM " . $this->db_prefix . "length_class_description lcd 
            WHERE p.length_class_id = lcd.length_class_id 
            AND lcd.language_id = :language_id) 
            AS length_class, 

            (SELECT AVG(rating) AS total FROM " . $this->db_prefix . "review r1 
            WHERE r1.product_id = p.product_id 
            AND r1.status = '1' 
            GROUP BY r1.product_id) 
            AS rating, 
    
            (SELECT COUNT(*) AS total FROM " . $this->db_prefix . "review r2 
            WHERE r2.product_id = p.product_id 
            AND r2.status = '1' 
            GROUP BY r2.product_id) 
            AS reviews, 

            p.sort_order FROM " . $this->db_prefix . "product p 
            LEFT JOIN " . $this->db_prefix . "product_description pd 
            ON (p.product_id = pd.product_id) 
            LEFT JOIN " . $this->db_prefix . "product_to_store p2s 
            ON (p.product_id = p2s.product_id) 
            LEFT JOIN " . $this->db_prefix . "manufacturer m 
            ON (p.manufacturer_id = m.manufacturer_id) 
            WHERE p.product_id = :product_id 
            AND pd.language_id = :language_id
            AND p.status = '1' 
            AND p.date_available <= NOW() 
            AND p2s.store_id = :store_id");

        $query_product->execute(array(
            ':user_group_id'    => $this->user_group_id,
            ':product_id'           => $product_id,
            ':language_id'          => $this->language_id, 
            ':store_id'             => $this->site_id,
        ));

        if ($query_product->rowCount()) {
            return $query_product->fetchObject('class_product');
        }
        return false;
   }
}	
